# https://www.zhipin.com/job_detail/{encryptJobId}.html?lid={lid}&securityId={securityId}&sessionId=

# 获取mysql表内数据，组合成url发起请求
import time
from selenium import webdriver
import pymysql
from bs4 import BeautifulSoup

req_list = []
# 连接数据库
try:
    connection = pymysql.connect(
        host='localhost',  # 数据库主机地址，根据实际情况修改
        user='root',  # 数据库用户名，根据实际情况修改
        password='123456',  # 数据库密码，根据实际情况修改
        database='zhipin_jobs',  # 数据库名称，根据实际情况修改
        charset='utf8mb4'  # 设置字符集，支持存储中文等更多字符
    )
    try:
        # 创建游标对象
        cursor = connection.cursor()

        # 执行SQL查询
        cursor.execute("SELECT * FROM joblist WHERE id BETWEEN 291 AND 300;")
        # cursor.execute("SELECT * FROM joblist;")

        # 获取所有记录
        results = cursor.fetchall()

        # 获取字段
        # encryptJobId 9, lid 12, securityId 1
        # jobName 11, brandName 38,
        # columns = cursor.description
        # for word in columns:
        #     print(word)

        # 打印结果
        for idx,row in enumerate(results):
            encryptJobId = row[9]
            lid = row[12]
            securityId = row[1]
            url = 'https://www.zhipin.com/job_detail/' + encryptJobId + '.html?lid=' + lid + '&securityId=' + securityId + '&sessionId='
            req_list.append(url)

            # 根据请求链接列表获取网页
            driver = webdriver.Chrome()
            driver.get(req_list[idx])
            time.sleep(10)
            html_source = driver.page_source
            print(req_list[idx])

            # print(html_source)

            # 存储网页数据
            bs = BeautifulSoup(html_source, "html.parser")

            all_values = [securityId, encryptJobId, lid]
            # 解析网页
            keywords = bs.find(attrs={"name":"keywords"})['content']
            description = bs.find(attrs={"name":"description"})['content']
            all_values.append(keywords)
            all_values.append(description)
            jobNameAndjobSalary = bs.find(attrs={"class":"name"})
            for ctx in jobNameAndjobSalary.children:
                if len(ctx.string) > 1:
                    all_values.append(ctx.string)
            if bs.find(attrs={"class":"text-desc text-city"}) is None:
                driver.close()
                continue
            cityName = bs.find(attrs={"class":"text-desc text-city"}).string
            jobExperience = bs.find(attrs={"class":"text-desc text-experiece"}).string
            jobDegree = bs.find(attrs={"class":"text-desc text-degree"}).string
            all_values.append(cityName)
            all_values.append(jobExperience)
            all_values.append(jobDegree)
            # 福利待遇
            jobtags = bs.find(attrs={"class":"tag-all job-tags"})
            if jobtags is None:
                all_values.append('')
            else:
                welfareList = '['
                for ctx in jobtags.children:
                    if len(ctx.string) > 1:
                        welfareList += '\'' + ctx.string + '\', '
                welfareLists = welfareList[:-2]
                welfareLists += ']'
                all_values.append(welfareLists)
            # 职位描述标签
            jobDetailTags = bs.find(attrs={"class":"job-keyword-list"})
            jobDetailTag = '['
            for ctx in jobDetailTags.children:
                if len(ctx.string) > 1:
                    jobDetailTag += '\'' + ctx.string + '\', '
            jobDetailTags = jobDetailTag[:-2]
            jobDetailTags += ']'
            all_values.append(jobDetailTags)

            # 职位描述
            jobDetail = bs.find(attrs={"class":"job-sec-text"}).text
            all_values.append(jobDetail)

            # 老板姓名 + 老板活跃时间
            bossName = bs.find_all("h2", {"class":"name"})[0].text.split('\n')
            flag = 0
            for ctx in bossName:
                if len(ctx) > 1:
                    all_values.append(ctx)
                    flag += 1
            if flag < 2:
                all_values.append('')
                flag = 0

            # 老板职位头衔
            bossTitle = bs.find(attrs={"class": "boss-info-attr"}).text
            bossTitle = bossTitle.strip()
            all_values.append(bossTitle)

            # 工商信息
            companyInfo = bs.find(attrs={"class":"level-list"})
            if companyInfo is None:
                for i in range(6):
                    all_values.append('')
            else:
                # 查找所有的span标签
                spans = companyInfo.find_all('span')
                # 移除span标签和内容
                for span in spans:
                    span.decompose()
                for ctx in companyInfo.children:
                    if len(ctx.text.strip()) >= 1:
                        all_values.append(ctx.text)

            # 公司介绍
            companyIntro = bs.find(attrs={"class":"job-sec-text fold-text"})
            all_values.append('' if companyIntro is None else companyIntro.text)

            # 工作地址
            locationAddress = bs.find(attrs={"class":"location-address"})
            all_values.append(locationAddress.text)

            # 经纬度
            # 查找div标签
            div_tag = bs.find('div', class_='job-location-map')
            # 获取data-lat属性的值
            data_lat = div_tag.get('data-lat')
            all_values.append(data_lat)

            # 将数组转换成元组存储到MySQL数据库里
            insertData = tuple(all_values)

            print(insertData)

            # 构建插入语句，注意要根据表结构和字段顺序来准确书写
            sql = """
            INSERT INTO jobdetail (
                securityId,
                encryptJobId,
                lid,
                keywords,
                jobDescription,
                jobName,
                jobSalary,
                cityName,
                jobExperience,
                jobDegree,
                welfareList,
                jobDetailTags,
                jobDetail,
                bossName,
                bossActiveTime,
                bossTitle,
                companyName,
                companyUser,
                resTime,
                companyType,
                manageState,
                companyFund,
                companyInfo,
                locationAddress,
                gpsLocation
            ) VALUES (
                %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
            )
            """
            cursor.execute(sql, insertData)
            driver.close()

        connection.commit()
    finally:
        connection.close()

except pymysql.MySQLError as e:
    print(f"数据库操作出错: {e}")




